import sys
from mysql_binary import MySQLName
from tkinter import *
from tkinter import ttk
import pandas as pd


class User:
    def __init__(self, window):
        self.add_db = None
        self.database_name = None
        self.account_ = None
        self.sql_input = None
        self.output_text = None
        self.ip = None
        self.ip_img = None
        self.password_img = None
        self.account_img = None
        self.connection = None
        self.window_name = window
        self.account = None
        self.password = None
        self.error_label = None

    def center_window(self, width, height):
        screen_width = self.window_name.winfo_screenwidth()
        screen_height = self.window_name.winfo_screenheight()
        x = (screen_width // 2) - (width // 2)
        y = (screen_height // 2) - (height // 2)
        self.window_name.geometry(f'{width}x{height}+{x}+{y}')

    def set_login_name(self):
        self.window_name.title("Mysql小工具")
        self.center_window(300, 350)
        self.window_name.iconbitmap("image/gongju.ico")
        self.window_name.config(background="#ffdde1")

        for i in range(6):
            self.window_name.rowconfigure(i, weight=1)
        for i in range(6):
            self.window_name.columnconfigure(i, weight=1)

        self.account_img = PhotoImage(file="image/账号.png")
        self.password_img = PhotoImage(file="image/密码.png")
        self.ip_img = PhotoImage(file="image/ip.png")

        Label(self.window_name, image=self.ip_img, bg="#ffdde1").grid(row=0, column=0, padx=(20, 0), sticky=W + E)
        self.ip = Entry(self.window_name)
        self.ip.grid(row=0, column=1, sticky=W + E)

        Label(self.window_name, image=self.account_img, bg="#ffdde1").grid(row=1, column=0, padx=(20, 0), sticky=W + E)
        self.account = Entry(self.window_name)
        self.account.grid(row=1, column=1, pady=5, sticky=W + E)

        Label(self.window_name, image=self.password_img, bg="#ffdde1").grid(row=2, column=0, padx=(20, 0), sticky=W + E)
        self.password = Entry(self.window_name, show='*')
        self.password.grid(row=2, column=1, sticky=W + E)

        Button(self.window_name, text="连接", width=8, cursor="hand2", command=self.on_connect, bg="#70f3ff").grid(
            row=3, column=1, sticky=W + E)

    def on_connect(self):
        self.account_ = self.account.get()
        password = self.password.get()
        ip = self.ip.get()
        self.connection = MySQLName(ip, password, self.account_)
        try:
            self.connection.connect()
            if self.error_label:
                self.error_label.destroy()
                self.error_label = None
            self.window_name.destroy()
            self.window_name = Tk()
            self.set_second_window()
            self.window_name.mainloop()
        except Exception as e:
            if self.error_label:
                self.error_label.config(text=f"连接失败！{e}, 请检查IP和密码。")
            else:
                self.error_label = Message(self.window_name, text="", width=150, bg="#e0eee8", fg="red")
                self.error_label.grid(row=4, column=1, pady=10, sticky=W + E)
                self.error_label.config(text=f"连接失败！{e}, 请检查输入的是否有误或者服务是否打开！。")

    def close(self):
        if self.connection is not None:
            self.connection.close()

    def set_second_window(self):
        self.window_name.title("Mysql小工具")
        self.center_window(1068, 681)
        self.window_name.iconbitmap("image/gongju.ico")

        # 添加菜单
        menubar = Menu(self.window_name, bg='#e3f9fd')
        file_menu = Menu(menubar, tearoff=0)
        file_menu.add_command(label="添加数据库", command=self.set_add_database)
        file_menu.add_separator()
        file_menu.add_command(label="退出", command=self.destroy_window)
        menubar.add_cascade(label="文件", menu=file_menu)

        menubar.add_command(label="执行", command=self.execute_sql)
        menubar.add_command(label="刷新", command=lambda: self.update_treeview(treeview))

        self.window_name.config(menu=menubar)

        area1 = PanedWindow(self.window_name, orient=HORIZONTAL)
        area1.pack(fill=BOTH, expand=1)

        left_frame = Frame(area1)
        treeview = ttk.Treeview(left_frame)
        treeview.heading('#0', text=f'用户:{self.account_}', anchor=W)
        treeview.pack(fill=BOTH, expand=1)

        databases = self.connection.show_databases()
        iid = 0
        for d in databases:
            db_item = treeview.insert('', END, iid=iid, text=d, open=False)
            iid += 1
            self.connection.choose_database(d)
            tables = self.connection.show_tables()
            for t in tables:
                treeview.insert(db_item, END, text=t)
            iid += 1

        treeview.bind("<ButtonRelease-1>", lambda event: self.data_mouseClick(event, treeview))

        area1.add(left_frame)

        area2 = Frame(area1)
        area1.add(area2)

        self.sql_input = Text(area2, height=10, fg='#b83b5e', bg='#eaffd0', font=("黑体", 16))
        self.sql_input.pack(fill=X, padx=10, pady=10)

        self.output_text = Text(area2, wrap=WORD, bg='#e3fdfd', font=("黑体", 16))
        self.output_text.pack(fill=BOTH, expand=True)

        self.output_text.tag_configure("error", foreground="red")

        area2.pack_propagate(False)
        self.sql_input.pack(side=TOP, fill=X)
        self.output_text.pack(side=TOP, fill=BOTH, expand=True)
        area2.config(height=681)

    def set_add_database(self):
        self.add_db = Toplevel(self.window_name)
        self.add_db.title("添加数据库")
        self.center_window(400, 200)
        self.add_db.iconbitmap("image/gongju.ico")
        self.add_db.config(background="#ffdde1")
        self.add_db.grab_set()  # 确保主窗口无法操作

        Label(self.add_db, text="数据库名:", bg="#ffdde1", font=("黑体", 16)).grid(row=1, column=0,
                                                                                   padx=(2, 0), pady=30,
                                                                                   sticky=W + E)
        self.database_name = Entry(self.add_db, font=("黑体", 16), fg="#b83b5e", )
        self.database_name.grid(row=1, column=1, pady=30, sticky=W + E)

        Button(self.add_db, text="添加", width=8, cursor="hand2", command=self.add_database, bg="#70f3ff",
               font=("黑体", 16)).grid(row=2, column=1, sticky=W + E)

    def add_database(self):
        result = self.database_name.get()
        try:
            self.connection.execute_query(f"create database {result}")
            self.output_text.delete(1.0, END)
            self.output_text.insert(END, f"{result}添加成功！请刷新！")
        except Exception as e:
            self.output_text.delete(1.0, END)
            self.output_text.insert(END, f"{result}添加失败！{e}！", "error")
        self.add_db.destroy()

    def execute_sql(self):
        sql = self.sql_input.get("1.0", END).strip()
        try:
            if not sql:
                raise Exception("执行语句为空！")
            if sql.lower().startswith("select"):
                result = self.connection.execute_query(sql)
                columns = [desc[0] for desc in self.connection.cursor.description]  # 获取列名
                if result:
                    df = pd.DataFrame(result, columns=columns)  # 使用列名创建 DataFrame
                    columns_str = "{:<10} " * len(df.columns) + "\n"
                    columns_str = columns_str.format(*df.columns)
                    for index, row in df.iterrows():
                        columns_str += "{:<10} " * len(row) + "\n"
                        columns_str = columns_str.format(*row)

                    self.output_text.delete(1.0, END)
                    self.output_text.insert(END, columns_str)
                else:
                    self.output_text.delete(1.0, END)
                    self.output_text.insert(END, "查询结果为空。")
            else:
                result = self.connection.execute_query(sql)
                self.output_text.delete(1.0, END)
                self.output_text.insert(END, f"执行成功！{result}")
        except Exception as e:
            self.output_text.delete(1.0, END)
            self.output_text.insert(END, f"执行失败！{e}", "error")

    def data_mouseClick(self, event, treeview):
        selected_items = treeview.selection()
        if not selected_items:
            return

        selected_item = selected_items[0]
        item_text = treeview.item(selected_item, 'text')
        parent_item = treeview.parent(selected_item)

        try:
            if parent_item:
                db_name = treeview.item(parent_item, 'text')
                self.connection.choose_database(db_name)
                table_content = self.connection.show_data(item_text)

                if table_content:
                    custom_columns = self.connection.get_columns(item_text)
                    df = pd.DataFrame(table_content, columns=custom_columns)

                    columns_str = "{:<10} " * len(df.columns) + "\n"
                    columns_str = columns_str.format(*df.columns)
                    for index, row in df.iterrows():
                        columns_str += "{:<10} " * len(row) + "\n"
                        columns_str = columns_str.format(*row)

                    self.output_text.delete(1.0, END)
                    self.output_text.insert(END, columns_str)
                else:
                    self.output_text.delete(1.0, END)
                    self.output_text.insert(END, f"表 {item_text} 没有数据。", "error")
                print(f"已选择表: {item_text}")
            else:
                self.connection.choose_database(item_text)
                self.output_text.delete(1.0, END)
                self.output_text.insert(END, f"已选择数据库: {item_text}。")

        except Exception as e:
            self.output_text.delete(1.0, END)
            self.output_text.insert(END, "数据不存在，请尝试刷新。", "error")
            print(f"错误: {e}")

    def update_treeview(self, treeview):
        databases = self.connection.show_databases()
        treeview.delete(*treeview.get_children())
        iid = 0
        for d in databases:
            db_item = treeview.insert('', END, iid=iid, text=d, open=False)
            iid += 1
            self.connection.choose_database(d)
            tables = self.connection.show_tables()
            for t in tables:
                treeview.insert(db_item, END, text=t)
            iid += 1

    def destroy_window(self):
        self.connection.close()
        self.window_name.destroy()

    def data_mouseClick(self, event, treeview):
        selected_items = treeview.selection()
        if not selected_items:
            return

        selected_item = selected_items[0]
        item_text = treeview.item(selected_item, 'text')
        parent_item = treeview.parent(selected_item)

        try:
            if parent_item:
                db_name = treeview.item(parent_item, 'text')
                self.connection.choose_database(db_name)
                table_content = self.connection.show_data(item_text)

                if table_content:
                    custom_columns = self.connection.get_columns(item_text)
                    df = pd.DataFrame(table_content, columns=custom_columns)

                    columns_str = "{:<10} " * len(df.columns) + "\n"
                    columns_str = columns_str.format(*df.columns)
                    for index, row in df.iterrows():
                        columns_str += "{:<10} " * len(row) + "\n"
                        columns_str = columns_str.format(*row)

                    self.output_text.delete(1.0, END)
                    self.output_text.insert(END, columns_str)
                else:
                    self.output_text.delete(1.0, END)
                    self.output_text.insert(END, f"表 {item_text} 没有数据。", "error")
                print(f"已选择表: {item_text}")
            else:
                self.connection.choose_database(item_text)
                self.output_text.delete(1.0, END)
                self.output_text.insert(END, f"已选择数据库: {item_text}。")

        except Exception as e:
            self.output_text.delete(1.0, END)
            self.output_text.insert(END, "数据不存在，请尝试刷新。", "error")
            print(f"错误: {e}")


def gui_start():
    init_window = Tk()
    my_gui = User(init_window)
    my_gui.set_login_name()
    init_window.mainloop()


if __name__ == '__main__':
    gui_start()
